#include <stdio.h>
#include <stdlib.h>
#include <conio.h>
#include <windows.h>
#include <mysql/mysql.h>
#include <time.h>

//MySQL variables
static char *sqlServer = "localhost"; /* server host (default=localhost) */
static char *sqlUser = "root"; /* username (default=login name) */
static char *sqlPassword = ""; /* password (default=none) */
static unsigned int sqlPort = 3306; /* port number (use built-in value) */
static char *sqlSocket = NULL; /* socket name (use built-in value) */
static char *sqlDb = "advanse"; /* database name (default=none) */
static unsigned int sqlFlags = 0; /* connection flags (none) */

typedef struct {
    int userProjectId;
    int pspId;
} Project;

typedef struct {
    int plannedTimeId;
	int planId;
	int planningTime;
	int designTime;
	int codeTime;
	int compileTime;
	int testTime;
	int postMortemTime;
	int totalTime;
} PlannedTime;

typedef struct {
    int number;
    char date[10];
    float planDirectHours;
    float planCumulativeHours;
    float planCumulativePlannedValue;
    float actualDirectHours;
    float actualCumulativeHours;
    float cumulativeEarnedValue;
    float adjustedEarnedValue;
} sptRow;

PlannedTime ppsGetActualTime(Project project)
{
    MYSQL *conn;
    MYSQL_RES *result;
    MYSQL_ROW row;
    MYSQL_FIELD *field;
    conn = mysql_init(NULL);
    int num_fields,i;
    
    PlannedTime actualTime;
    float delta;
    time_t start, end;
    int counter;
    
    if(mysql_real_connect(conn, sqlServer, sqlUser, sqlPassword, sqlDb, 0, NULL, 0) != NULL)
    {
        
        for(counter=1; counter<=6; counter++)
        {
            //Traverse phases
            char check[1000];
            sprintf(check, "SELECT * FROM timelog WHERE user_project_id = %d AND phase_id = %d", project.userProjectId, counter);
            
            if(mysql_query(conn, check)==0)
            {
                result = mysql_store_result(conn);
                num_fields = mysql_num_fields(result);
                while ((row = mysql_fetch_row(result)))
                {
                    for(i = 0; i < num_fields; i++)
                    {
                        char value[100];
                        sprintf(value,"%s", row[i] ? row[i] : "NULL");
                        
                        switch(i)
                        {
                            case 3: start = atol(value);
                                break;
                            case 4: end = atol(value);
                                break;
                        }
                    }
                }
                
                delta = difftime(end,start) / 60; // convert this to minutes
                
                switch(counter)
                {
                    case 1: actualTime.planningTime = delta;
                        break;
                    case 2: actualTime.designTime = delta;
                        break;
                    case 3: actualTime.codeTime = delta;
                        break;
                    case 4: actualTime.compileTime = delta;
                        break;
                    case 5: actualTime.testTime = delta;
                        break;
                    case 6: actualTime.postMortemTime = delta;
                        break;
                }
            }
            //end
        }
        
    }
    else
    {
        printf("DATABASE CONNECTION ERROR!");
    }
    
    mysql_free_result(result);
    mysql_close(conn);
    
    return actualTime;
}

int ppsGetTotalActualTime(Project project)
{
    float total;
    PlannedTime actualTime = ppsGetActualTime(project);
    total = actualTime.planningTime + 
        actualTime.designTime +
        actualTime.codeTime +
        actualTime.compileTime +
        actualTime.testTime +
        actualTime.postMortemTime;
        
    //printf("TOTAL ACTUAL TIME!!! %f\n", total);
    
    return 0;
}

int sptGetTaskPlanIDFromProject(Project project)
{
    MYSQL *conn;
    MYSQL_RES *result;
    MYSQL_ROW row;
    MYSQL_FIELD *field;
    conn = mysql_init(NULL);
    int num_fields,i;
    
    int id;
    
    if(mysql_real_connect(conn, sqlServer, sqlUser, sqlPassword, sqlDb, 0, NULL, 0) != NULL)
    {  
        char check[1000];
        sprintf(check, "SELECT * FROM taskplan WHERE user_project_id = %d", project.userProjectId);
        mysql_query(conn, check);
        result = mysql_store_result(conn);
        
        num_fields = mysql_num_fields(result);
        while ((row = mysql_fetch_row(result)))
        {
            for(i = 0; i < num_fields; i++)
            {
                char value[100];
                sprintf(value,"%s", row[i] ? row[i] : "NULL");
                
                switch(i)
                {
                    case 0: id = atoi(value);
                        break;
                    case 1:
                        break;
                }
            }
        }
    }
    else
    {
        printf("DATABASE CONNECTION ERROR!");
    }
    
    mysql_free_result(result);
    mysql_close(conn);
    
    return id;
}

int ppsGetPlanIDFromProject(Project project)
{
    MYSQL *conn;
    MYSQL_RES *result;
    MYSQL_ROW row;
    MYSQL_FIELD *field;
    conn = mysql_init(NULL);
    int num_fields,i;
    
    int id;
    
    if(mysql_real_connect(conn, sqlServer, sqlUser, sqlPassword, sqlDb, 0, NULL, 0) != NULL)
    {  
        char check[1000];
        sprintf(check, "SELECT * FROM plansummary WHERE user_project_id = %d", project.userProjectId);
        mysql_query(conn, check);
        result = mysql_store_result(conn);
        
        num_fields = mysql_num_fields(result);
        while ((row = mysql_fetch_row(result)))
        {
            for(i = 0; i < num_fields; i++)
            {
                char value[100];
                sprintf(value,"%s", row[i] ? row[i] : "NULL");
                
                switch(i)
                {
                    case 0: id = atoi(value);
                        break;
                    case 1:
                        break;
                }
            }
        }
    }
    else
    {
        printf("DATABASE CONNECTION ERROR!");
    }
    
    mysql_free_result(result);
    mysql_close(conn);
    
    return id;
}

float sptGetHoursFromPhase(Project project, int phase_id)
{
    MYSQL *conn;
    MYSQL_RES *result;
    MYSQL_ROW row;
    MYSQL_FIELD *field;
    conn = mysql_init(NULL);
    int num_fields,i;
    
    float hours;
    
    if(mysql_real_connect(conn, sqlServer, sqlUser, sqlPassword, sqlDb, 0, NULL, 0) != NULL)
    {  
        char check[1000];
        sprintf(check, "SELECT * FROM plannedtime WHERE plan_id = %d", ppsGetPlanIDFromProject(project));
        mysql_query(conn, check);
        result = mysql_store_result(conn);
        
        num_fields = mysql_num_fields(result);
        while ((row = mysql_fetch_row(result)))
        {
            for(i = 0; i < num_fields; i++)
            {
                char value[100];
                sprintf(value,"%s", row[i] ? row[i] : "NULL");
                
                switch(i)
                {
                    case 2: if(phase_id == 1 ) hours = atof(value) / 60;
                        break;
                    case 3: if(phase_id == 2 ) hours = atof(value) / 60;
                        break;
                    case 4: if(phase_id == 3 ) hours = atof(value) / 60;
                        break;
                    case 5: if(phase_id == 4 ) hours = atof(value) / 60;
                        break;
                    case 6: if(phase_id == 5 ) hours = atof(value) / 60;
                        break;
                    case 7: if(phase_id == 6 ) hours = atof(value) / 60;
                        break;
                }
            }
        }
    }
    else
    {
        printf("DATABASE CONNECTION ERROR!");
    }
    
    mysql_free_result(result);
    mysql_close(conn);
    
    return hours;
}

float sptGetTotalHoursPPS(Project project, int phase_id)
{
    MYSQL *conn;
    MYSQL_RES *result;
    MYSQL_ROW row;
    MYSQL_FIELD *field;
    conn = mysql_init(NULL);
    int num_fields, i;
    
    float hours;
    
    if(mysql_real_connect(conn, sqlServer, sqlUser, sqlPassword, sqlDb, 0, NULL, 0) != NULL)
    {  
        char check[1000];
        sprintf(check, "SELECT * FROM plannedtime WHERE plan_id = %d", ppsGetPlanIDFromProject(project));
        mysql_query(conn, check);
        result = mysql_store_result(conn);
        
        num_fields = mysql_num_fields(result);
        while ((row = mysql_fetch_row(result)))
        {
            for(i = 0; i < num_fields; i++)
            {
                char value[100];
                sprintf(value,"%s", row[i] ? row[i] : "NULL");
                
                switch(i)
                {
                    case 2: if(phase_id == 1 ) hours = atof(value) / 60;
                        break;
                    case 3: if(phase_id == 2 ) hours = atof(value) / 60;
                        break;
                    case 4: if(phase_id == 3 ) hours = atof(value) / 60;
                        break;
                    case 5: if(phase_id == 4 ) hours = atof(value) / 60;
                        break;
                    case 6: if(phase_id == 5 ) hours = atof(value) / 60;
                        break;
                    case 7: if(phase_id == 6 ) hours = atof(value) / 60;
                        break;
                }
            }
        }
    }
    else
    {
        printf("DATABASE CONNECTION ERROR!");
    }
    
    mysql_free_result(result);
    mysql_close(conn);
    
    return hours;
}

float sptGetActualHoursFromPhase(Project project, int phase_id)
{
    MYSQL *conn;
    MYSQL_RES *result;
    MYSQL_ROW row;
    MYSQL_FIELD *field;
    conn = mysql_init(NULL);
    int num_fields,i;
    
    time_t start, end;
    float hours;
    
    if(mysql_real_connect(conn, sqlServer, sqlUser, sqlPassword, sqlDb, 0, NULL, 0) != NULL)
    {  
        char check[1000];
        sprintf(check, "SELECT start_time, end_time FROM timelog WHERE user_project_id = %d AND phase_id = %ld", project.userProjectId, phase_id);
        mysql_query(conn, check);
        result = mysql_store_result(conn);
        
        num_fields = mysql_num_fields(result);
        while ((row = mysql_fetch_row(result)))
        {
            for(i = 0; i < num_fields; i++)
            {
                char value[100];
                sprintf(value,"%s", row[i] ? row[i] : "NULL");
                
                switch(i)
                {
                    case 0: start = atol(value);
                        break;
                    case 1: end = atol(value);
                        break;
                }
            }
        }
    }
    
    mysql_free_result(result);
    mysql_close(conn);
    
    hours = (difftime(end,start) / 60) / 60;
    return hours;
}

sptRow * sptGetRows(Project project)
{
    time_t date_planned;
    struct tm *date;
    char buffer[10];
    int counter = 0;
    long * dates;
    float hours;
    
    sptRow * sptRowData;
    
    MYSQL *conn;
    MYSQL_RES *result;
    MYSQL_ROW row;
    MYSQL_FIELD *field;
    conn = mysql_init(NULL);
    
    int num_fields, num_rows, i;
    
    if(mysql_real_connect(conn, sqlServer, sqlUser, sqlPassword, sqlDb, 0, NULL, 0) != NULL)
    {  
        char check[1000];
        sprintf(check, "SELECT DISTINCT date_planned FROM taskplanentry WHERE taskplan_id = %d", sptGetTaskPlanIDFromProject(project));
        mysql_query(conn, check);
        result = mysql_store_result(conn);
        
        num_fields = mysql_num_fields(result);
        num_rows = mysql_num_rows(result);
        
        sptRowData = malloc(num_rows*sizeof(sptRowData));
        dates = (long*) malloc(num_rows*sizeof(long));
        
        while ((row = mysql_fetch_row(result)))
        {
            for(i = 0; i < num_fields; i++)
            {
                char value[100];
                sprintf(value,"%s", row[i] ? row[i] : "NULL");
                dates[counter] = atol(value);
            }
            counter++;
        }
    }
    
    mysql_free_result(result);
    mysql_close(conn);
    
    //get the number, and date
    for(i=0; i<counter; i++)
    {
        hours=0;
        date_planned = dates[i];
        date = localtime(&date_planned);
        sprintf(buffer,"%d/%d/%d",date->tm_mon+1,date->tm_mday,date->tm_year+1900);
        
        sptRowData[i].number = i+1;
        //printf("D%d\n", sptRowData[i].number);
        strcpy(sptRowData[i].date,buffer);
        //printf("DATE: %s\n",sptRowData[i].date);        
        printf("D%d PLANNED DIRECT HOURS: ",sptRowData[i].number);
        scanf("%f", &sptRowData[i].planDirectHours);
        
        if(i > 0)
        {
            sptRowData[i].planCumulativeHours = sptRowData[i-1].planDirectHours + sptRowData[i].planDirectHours;
            //printf("PLANNED CUMULATIVE HOURS: %f",sptRowData[i].planCumulativeHours);
        }
        else
        {
            sptRowData[i].planCumulativeHours = sptRowData[i].planDirectHours;
            //printf("PLANNED CUMULATIVE HOURS: %f",sptRowData[i].planCumulativeHours);
        }
        
        // calculate cumulative hours from plannedtime
        conn = mysql_init(NULL);
        if(mysql_real_connect(conn, sqlServer, sqlUser, sqlPassword, sqlDb, 0, NULL, 0) != NULL)
        {  
            char check[1000];
            int ii;
            sprintf(check, "SELECT phase_id FROM taskplanentry WHERE date_planned = %ld", dates[i]);
            mysql_query(conn, check);
            result = mysql_store_result(conn);
            
            num_fields = mysql_num_fields(result);
            num_rows = mysql_num_rows(result);
            
            while ((row = mysql_fetch_row(result)))
            {
                for(ii=0; ii<num_fields; ii++)
                {
                    char value[100];
                    sprintf(value,"%s", row[ii] ? row[ii] : "NULL");
                    hours += sptGetHoursFromPhase(project, atoi(value));
                    
                    if(sptRowData[i].planCumulativeHours >= hours)
                    {
                        sptRowData[i].planCumulativePlannedValue = hours;
                    }
                }
            }
        }
        
        //printf("\nCUMULATIVE PLANNED VALUE: %f\n", sptRowData[i].planCumulativePlannedValue);
        
        // calculate actual direct hours from time log
        hours = 0;
        conn = mysql_init(NULL);
        if(mysql_real_connect(conn, sqlServer, sqlUser, sqlPassword, sqlDb, 0, NULL, 0) != NULL)
        {  
            char check[1000];
            int iii;
            sprintf(check, "SELECT phase_id FROM taskplanentry WHERE date_planned = %ld", dates[i]);
            mysql_query(conn, check);
            result = mysql_store_result(conn);
            
            num_fields = mysql_num_fields(result);
            num_rows = mysql_num_rows(result);
            
            while ((row = mysql_fetch_row(result)))
            {
                for(iii=0; iii<num_fields; iii++)
                {
                    char value[100];
                    sprintf(value,"%s", row[iii] ? row[iii] : "NULL");
                    hours += sptGetActualHoursFromPhase(project, atoi(value));
                }
            }
        }
        
        sptRowData[i].actualDirectHours = hours;
        //printf("ACTUAL DIRECT HOURS: %f\n", sptRowData[i].actualDirectHours);
        
        if(i > 0)
        {
            sptRowData[i].actualCumulativeHours = sptRowData[i-1].actualDirectHours + sptRowData[i].actualDirectHours;
            //printf("ACTUAL CUMULATIVE HOURS: %f\n",sptRowData[i].actualCumulativeHours);
        }
        else
        {
            sptRowData[i].actualCumulativeHours = sptRowData[i].actualDirectHours;
            //printf("ACTUAL CUMULATIVE HOURS: %f\n",sptRowData[i].actualCumulativeHours);
        }
        
        // calculate cumulative earned value
        hours = 0;
        conn = mysql_init(NULL);
        if(mysql_real_connect(conn, sqlServer, sqlUser, sqlPassword, sqlDb, 0, NULL, 0) != NULL)
        {  
            char check[1000];
            int iiii;
            sprintf(check, "SELECT phase_id FROM taskplanentry WHERE date_planned = %ld", dates[i]);
            mysql_query(conn, check);
            result = mysql_store_result(conn);
            
            num_fields = mysql_num_fields(result);
            num_rows = mysql_num_rows(result);
            
            while ((row = mysql_fetch_row(result)))
            {
                for(iiii=0; iiii<num_fields; iiii++)
                {
                    char value[100];
                    sprintf(value,"%s", row[iiii] ? row[iiii] : "NULL");
                    hours += sptGetActualHoursFromPhase(project, atoi(value));
                }
            }
        }
        
        ppsGetTotalActualTime(project);
        
        //printf("ACTUAL CUMULATIVE EARNED VALUE: \n");        
        //printf("ADJUSTED EARNED VALUE: \n");
        
        sptGetActualHoursFromPhase(project,1);
        
        //printf("\n");
        mysql_free_result(result);
        mysql_close(conn);
        
    }
    
    free(dates);
    
    return sptRowData;
}

void sptGenerateSchedulePlanningTemplate()
{
    
}

void sptMain()
{
    Project project;
    project.userProjectId = 1;
    sptRow * sptRowData;
    
    sptRowData = sptGetRows(project);
    
    /*
    printf("\nWeek/Day Number: \n");
    printf("Date: \n");
    printf("Direct Hours: \n");
    printf("Cumulative Hours: \n");
    printf("Cumulative Planned Value: \n");
    printf("Direct Hours: \n");
    printf("Cumulative Hours: \n");
    printf("Cumulative Earned Value: \n");
    printf("Adjusted Earned Value: \n");
    */
    
    getch();
}

int main()
{
    sptMain();
    return 0;
}
